package com.firewolf.hibernate;

import java.util.ArrayList;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;
import org.hibernate.criterion.Example;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.service.ServiceRegistryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

public class MainDriver {
	private Configuration cfg;
	private SessionFactory sessionFactory;
	private Session session;
	private Transaction transaction;

	/**
	 * @Description: Query By Example @输入和输出示例： @思路：
	 */
	/***************************QBE***************************/
	@Test
	public void QBE() {
		openSession();
		// 查询全部
		Criteria criteria = session.createCriteria(Grade.class);
		List list = criteria.list();
		System.out.println(list);
		// 设置一个模板，创建一个模版，比如Grade有desci，我设置g.setDesci("best
		// class"),则这个表中的所有的desci为best class的数据都会出来
		Grade g = new Grade();
		g.setDesci("best class");
		criteria.add(Example.create(g));
		List list2 = criteria.list();
		System.out.println(list2);
		closeSession();
	}
	
	/**
	 * @Description:
	 	Query By Criteria，主要有Criteria,Projections,Oder,Restrictions类组成
	 * @输入和输出示例：
	 * @思路：
	 */
	/*************************QBC************************************/
	/**
	 * 查询全部属性,
	 */
	@Test
	public void allProperties(){
		openSession();
		Criteria criteria = session.createCriteria(Grade.class);
		List list = criteria.list(); //多个对象
//		Grade g = (Grade) criteria.uniqueResult();//如果知道结果只有一个，可以这样查询
		closeSession();
		System.out.println(list);
	}
	
	/**
	 * 根据条件查询,使用Restrictions添加条件
	 * 比较：
	 * 		eq:等于
	 * 		ne:不等于，也可以是：Restrictions.not(Exprission.eq()) 
	 * 		le:小于等于
	 * 		ge:大于等于
	 * 		lt:小于
	 * 		gt:大于
	 * 逻辑操作：
	 * 		or:或条件
	 * 		and：与
	 * 		not:非，取反，如Restrictions.not(Restrictions.in("id", ids)))表示id not in ....
	 * in:在什么范围内
	 * between: between ... and ...
	 * like: 模糊查询
	 * Criteria.addOrder(Order.descr(propertyName)):排序
	 * criteria.setProjection(Projections.rowCount()):聚合函数
	 */
	@Test
	public void conditions(){
		openSession();
		Criteria criteria = session.createCriteria(Grade.class);
		//使用add添加查询条件，调用多次add，相当于多个条件之间使用and连接
		criteria.add(Restrictions.eq("name", "1503JavaD"));//等于
		criteria.add(Restrictions.ne("name", "1504"));//不等于
	
		//or连接，调用Restrictions.or
		criteria.add(Restrictions.or(Restrictions.eq("name", "1503JavaD"),Restrictions.idEq(2)));
		
		//查询条件in 和 not in
		Collection<Integer> ids = new ArrayList<>();
		ids.add(1);
		ids.add(3);
		
		criteria.add(Restrictions.in("id", ids));
		criteria.add(Restrictions.not(Restrictions.in("id", ids)));
		
		//between and 
		criteria.add(Restrictions.between("id", 1, 2));
		
		//模糊查询like
		criteria.add(Restrictions.like("name", "%1503%"));
		
		//排序
		criteria.addOrder(Order.desc("id"));
		
		//分页
		criteria.setFirstResult(0);
		criteria.setMaxResults(3);
		
		List list = criteria.list();
		System.out.println(list);
		
		//聚合函数,sum,count,min,max,avg,等
		Long count = (Long) criteria.setProjection(Projections.rowCount()).uniqueResult();//求行数
		Long sum = (Long) criteria.setProjection(Projections.sum("id")).uniqueResult();
		System.out.println(count+","+sum);
		
		closeSession();
	
	}

	/***
	 * @Description: @输入和输出示例： @思路：
	 */
	@Test
	public void critire() {
		openSession();
		Criteria criteria = session.createCriteria(Grade.class);
		// 查所有的
		// List<Grade> list = criteria.list();
		// System.out.println(list);

		// 添加查询条件，可以添加多个，多个之间默认使用and连接
		 criteria.add(Restrictions.eq("name", "1503JavaD"));//等于条件
		 criteria.add(Restrictions.ne("name", "1503JavaD"));//不等于条件<>
		// criteria.add(Restrictions.eq("id", 2));//ID等于某个值
		 
		// Grade g = (Grade) criteria.uniqueResult();
		 
		// 或条件
		// criteria.add(Restrictions.or(Restrictions.eq("name", "1503JavaD"),
		// Restrictions.eq("id", 2)));

		// 模糊查询
		criteria.add(Restrictions.like("name", "%JavaD%"));

		// 排序
		criteria.addOrder(Order.asc("name"));

		// 函数
		criteria.setProjection(Projections.rowCount());
		criteria.setProjection(Projections.count("name"));

		// 分页：
		criteria.setFirstResult(0);
		criteria.setMaxResults(9);

		List l = criteria.list();

		System.out.println(l);
		closeSession();
	}

	@Test
	public void addData() throws Exception {
		openSession();
		Student s1 = new Student("zhangsan", "1",20,"english is verry good");
		Student s2 = new Student("lisi", "2",30,"math is bad");
		Set<Student> ss = new HashSet<>();
		ss.add(s1);
		ss.add(s2);
		Grade g1 = new Grade("1503JavaD", "best class",90);
		g1.setStudents(ss);

		session.save(g1);
		session.save(s1);
		session.save(s2);

		Student s11 = new Student("wangwu", "2",40,"is not good at chinese");
		Student s22 = new Student("wanggang", "1",18,"this is a bad student");
		Set<Student> ss1 = new HashSet<>();
		ss1.add(s11);
		ss1.add(s22);
		Grade g2 = new Grade("1503JavaD1", "bad class",30);
		g2.setStudents(ss1);

		session.save(s11);
		session.save(s22);
		session.save(g2);

		closeSession();
	}

	private void openSession() {
		session = sessionFactory.openSession();
		transaction = session.beginTransaction();
	}

	private void closeSession() {
		transaction.commit();
		session.close();
	}

	@Before
	public void init() {
		cfg = new Configuration().configure("com/firewolf/hibernate/hibernate.cfg.xml");
		sessionFactory = cfg.buildSessionFactory(
				new ServiceRegistryBuilder().applySettings(cfg.getProperties()).buildServiceRegistry());
		System.out.println("创建SessionFactory");
	}

	@After
	public void destory() {
		sessionFactory.close();
		System.out.println("关闭SessionFactory");
	}

}
